﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Web.UI.WebControls;
using System.IO;
using System.Web.UI;
using System.Globalization;
using System.Collections;
using Microsoft.Office.Interop.Owc11;
using System.Web;
using System.Data.OleDb;
namespace ART.Common
{

    //*******************DEMO*******************

    //string[] columns = new string[] { "ID","Name" };
    //string[] captions = new string[] { "编号","名称" };
    //ART.Common.JExcel.ExportExcel(ds.Tables[1], columns,captions,"123");

    //*******************DEMO*******************


    /// <summary>
    /// 输出Excel类
    /// </summary>
    public class JExcel
    {


        /// <summary>
        /// 导入Excel 
        /// </summary>
        /// <param name="fileurl">excel路径地址</param>
        /// <returns></returns>
        public static DataTable ImportExcel(string fileurl)
        {
            return ImportExcel(fileurl, "Sheet1","*");
        }

        /// <summary>
        /// 导入Excel 
        /// </summary>
        /// <param name="fileurl">excel路径地址</param>
        /// <param name="table">表</param>
        /// <returns></returns>
        public static DataTable ImportExcel(string fileurl,string table)
        {
            return ImportExcel(fileurl, table,"*");
        }
        
        /// <summary>
        /// 导入Excel 
        /// </summary>
        /// <param name="fileurl">excel路径地址</param>
        /// <param name="table">表</param>
        /// <param name="fileds">字段</param>
        /// <returns></returns>
        public static DataTable ImportExcel(string fileurl, string table,string fileds)
        {
            var strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";", fileurl);
            //var strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", fileurl);//Excel2003
            //string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + xlsPath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"; //Excel2007

            DataTable dt = new DataTable();
            using (var oledbConn = new OleDbConnection(strConn))
            {
                oledbConn.Open();
                OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format("SELECT {0} FROM [{1}$]",fileds,table), strConn);
                adapter.Fill(dt);
                oledbConn.Close();
            }
            return dt;

        }





        /// <summary>
        /// 通过XML方式输出数据集的Excel文件 fileName不带扩展名
        /// </summary>
        /// <param name="dt">数据集</param>
        public static void ExportExcel(DataTable dt, string[] columns, string[] captions, string fileName)
        {
            OWC_Excel owc = new OWC_Excel();

            dt = ChangeTableColumns(dt, columns);

            string excel = owc.BindExcelXML(dt, captions);

            ExportExcelFile(excel, fileName);
        }



        /// <summary>
        /// 在asp.net中向客户端输出xls文件的基础方法
        /// </summary>
        /// <param name="excel">execlexml数据</param>
        /// <param name="fileName">文件名称</param>
        private static void ExportExcelFile(string excel, string fileName)
        {
            System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
            response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            response.ContentType = "application/vnd.ms-excel";
            response.Charset = "gb2312";
            response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
            response.Write(excel);
            response.End();
        }



        /// <summary>
        /// 筛选要输入的数据集中指定的列
        /// </summary>
        /// <param name="dt">输出的数据集</param>
        /// <param name="columns">要输出的列</param>
        private static DataTable ChangeTableColumns(DataTable dt, string[] columns)
        {



            DataTable newdt = new DataTable();

            for (int i = 0; i < columns.Length; i++) //列排序
            {
                newdt.Columns.Add(columns[i] + "0");
            }
            for (int k = 0; k < dt.Rows.Count; k++)
            {
                DataRow newdr = newdt.NewRow();

                for (int i = 0; i < columns.Length; i++)
                {
                    try
                    {
                        newdr[columns[i] + "0"] = dt.Rows[k][columns[i]];
                    }
                    catch
                    {
                        newdr[columns[i] + "0"] = "";
                    }
                }
                newdt.Rows.Add(newdr);

            }
            return newdt;

        }
    }

    public class OWC_Excel
    {

        #region 构造函数

        public OWC_Excel()
            : this(OWC_Excel.FontSize.small)
        {

        }

        public OWC_Excel(FontSize fs)
        {
            this._FontSize = fs;

            #region 生成代码实例
            /*
			<html xmlns:x="urn:schemas-microsoft-com:office:excel"
			xmlns="http://www.w3.org/TR/REC-html40">

			<head>
			<style type="text/css">
			<!--tr
				{mso-height-source:auto;}
			td
				{white-space:nowrap;}
			.wc4680812
				{white-space:nowrap;
				font-family:宋体;
				mso-number-format:General;
				font-size:12pt;													*************_xmlFontSize
				font-weight:auto;
				font-style:auto;
				text-decoration:auto;
				mso-background-source:auto;
				mso-pattern:auto;
				mso-color-source:auto;
				text-align:general;
				vertical-align:bottom;
				border-top:none;
				border-left:none;
				border-right:none;
				border-bottom:none;
				mso-protection:locked;}
			-->
			</style>
			</head>		
			
			<body>
			<!--[if gte mso 9]><xml>
			<x:ExcelWorkbook>
			<x:ExcelWorksheets>
			<x:ExcelWorksheet>
				<x:OWCVersion>9.0.0.3821</x:OWCVersion>
				<x:Label Style='border-top:solid .5pt silver;border-left:solid .5pt silver;
				border-right:solid .5pt silver;border-bottom:solid .5pt silver'>
				<x:Caption>Microsoft Office Spreadsheet</x:Caption>
				</x:Label>
				<x:Name>Sheet1</x:Name>
				<x:WorksheetOptions>
				<x:Selected/>
				<x:Height>7620</x:Height>								***********************_xmlX_Height
				<x:Width>15240</x:Width>								**********************_xmlX_Width
				<x:TopRowVisible>0</x:TopRowVisible>
				<x:LeftColumnVisible>0</x:LeftColumnVisible>
				<x:ProtectContents>False</x:ProtectContents>
				<x:DefaultRowHeight>210</x:DefaultRowHeight>			**********************_xmlX_DefaultRowHeight
				<x:StandardWidth>2389</x:StandardWidth>
				</x:WorksheetOptions>
			</x:ExcelWorksheet>
			</x:ExcelWorksheets>
			<x:MaxHeight>80%</x:MaxHeight>
			<x:MaxWidth>80%</x:MaxWidth>
			</x:ExcelWorkbook>
			</xml><![endif]-->
		
			<table class=wc4680812 x:str>
			<col class=wc4680812 width="56">
			<col class=wc4680812 width="56">
			<col class=wc4680812 width="56">
			<col class=wc4680812 width="56">
			<col class=wc4680812 width="56">
			<col class=wc4680812 width="56">
			<tr height="18">											****************************_xmlTr_height
			<td class=wc4680812 x:num="1"></td>
			<td class=wc4680812 x:num="2"></td>
			<td class=wc4680812 x:num="3"></td>
			<td class=wc4680812 x:num="4"></td>
			<td class=wc4680812 x:num="5"></td>
			<td class=wc4680812 x:num="6"></td>
			</tr>
			<tr height="18">
			<td class=wc4680812 x:num="11"></td>
			<td class=wc4680812 x:num="22"></td>
			<td class=wc4680812 x:num="33"></td>
			<td class=wc4680812 x:num="44"></td>
			<td class=wc4680812 x:num="55"></td>
			<td class=wc4680812 x:num="66"></td>
			</tr>
			<tr height="18">
			<td class=wc4680812 x:num="111"></td>
			<td class=wc4680812 x:num="222"></td>
			<td class=wc4680812 x:num="333"></td>
			<td class=wc4680812 x:num="444"></td>
			<td class=wc4680812 x:num="555"></td>
			<td class=wc4680812 x:num="666"></td>
			</tr>
			<tr height="14">
			<td class=wc4680812>aaa</td>
			<td class=wc4680812>bbb</td>
			<td class=wc4680812>ccc</td>
			<td class=wc4680812>dddd</td>
			<td class=wc4680812>eeee</td>
			<td class=wc4680812>ffff</td>
			</tr>
			</table>

			</body>

			</html>
			*/
            #endregion

            StringBuilder sb = new StringBuilder();

            sb.Append("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
            sb.Append("xmlns=\"http://www.w3.org/TR/REC-html40\">");
            sb.Append("");
            sb.Append("<head>");
            sb.Append("<meta http-equiv='Content-Type' content='text/html; charset=gb2312'>");
            sb.Append("<style type=\"text/css\">");
            sb.Append("<!--tr");
            sb.Append("	{mso-height-source:auto;}");
            sb.Append("td");
            sb.Append("	{white-space:nowrap;}");
            sb.Append(".wc4680812");
            sb.Append("	{white-space:nowrap;");
            sb.Append("	font-family:宋体;");
            sb.Append("	mso-number-format:General;");
            //sb.Append("	font-size:10pt;");
            sb.Append("	font-size:" + this._xmlFontSize + ");");
            sb.Append("	font-weight:auto;");
            sb.Append("	font-style:auto;");
            sb.Append("	text-decoration:auto;");
            sb.Append("	mso-background-source:auto;");
            sb.Append("	mso-pattern:auto;");
            sb.Append("	mso-color-source:auto;");
            sb.Append("	text-align:center;");
            sb.Append("	vertical-align:bottom;");
            sb.Append("	border-top:none;");
            sb.Append("	border-left:none;");
            sb.Append("	border-right:none;");
            sb.Append("	border-bottom:none;");
            sb.Append("	mso-protection:locked;}");
            sb.Append("-->");
            sb.Append("</style>");
            sb.Append("</head>");
            sb.Append("");
            sb.Append("<body>");
            sb.Append("<!--[if gte mso 9]><xml>");
            sb.Append(" <x:ExcelWorkbook>");
            sb.Append("  <x:ExcelWorksheets>");
            sb.Append("   <x:ExcelWorksheet>");
            sb.Append("    <x:OWCVersion>9.0.0.3821</x:OWCVersion>");
            sb.Append("    <x:Label Style='border-top:solid .5pt silver;border-left:solid .5pt silver;");
            sb.Append("     border-right:solid .5pt silver;border-bottom:solid .5pt silver'>");
            sb.Append("     <x:Caption>Microsoft Office Spreadsheet</x:Caption>");
            sb.Append("    </x:Label>");
            sb.Append("    <x:Name>Sheet1</x:Name>");
            sb.Append("    <x:WorksheetOptions>");
            sb.Append("     <x:Selected/>");
            //sb.Append("     <x:Height>7620</x:Height>");
            sb.Append("     <x:Height>" + this._xmlX_Height + "</x:Height>");
            //sb.Append("     <x:Width>15240</x:Width>");
            sb.Append("     <x:Width>" + this._xmlX_Width + "</x:Width>");
            sb.Append("     <x:TopRowVisible>0</x:TopRowVisible>");
            sb.Append("     <x:LeftColumnVisible>0</x:LeftColumnVisible>");
            sb.Append("     <x:ProtectContents>False</x:ProtectContents>");
            //sb.Append("     <x:DefaultRowHeight>210</x:DefaultRowHeight>");
            sb.Append("     <x:DefaultRowHeight>" + this._xmlX_DefaultRowHeight + "</x:DefaultRowHeight>");
            sb.Append("     <x:StandardWidth>2389</x:StandardWidth>");
            sb.Append("    </x:WorksheetOptions>");
            sb.Append("   </x:ExcelWorksheet>");
            sb.Append("  </x:ExcelWorksheets>");
            sb.Append("  <x:MaxHeight>80%</x:MaxHeight>");
            sb.Append("  <x:MaxWidth>80%</x:MaxWidth>");
            sb.Append(" </x:ExcelWorkbook>");
            sb.Append("</xml><![endif]-->");

            _strXmlLeft = sb.ToString();

            sb = new StringBuilder();

            sb.Append("</table>");
            sb.Append("");
            sb.Append("</body>");
            sb.Append("");
            sb.Append("</html>");

            _strXmlRight = sb.ToString();

            _strXmlRowLeft = "<tr height=\"" + this._xmlTr_height + "\">";
            _strXmlRowRight = "</tr>";
            _strXmlCellLeft = "<td>";
            _strXmlCellRight = "</td>";
            _strXmlCellLeft_Number = "<td x:num=\"";
            _strXmlCellRight_Number = "\"></td>";
        }

        #endregion


        /// <summary>
        /// 根据DataTable中的内容生成XML
        /// </summary>
        /// <param name="dt">数据集</param>
        /// <returns></returns>
        public string BindExcelXML(DataTable dt, string[] captions)
        {
            //int i, j;
            //DataRow dr;
            DateTime dtTemp = DateTime.Now;
            StringBuilder sbTemp = new StringBuilder();

            if (dt.Columns.Count == 0)
            {
                return GetEmptyXML();
            }
            else
            {
                sbTemp.Append(strXmlLeft);
                sbTemp.Append(GetTableTitle(dt.Columns.Count));



                sbTemp.Append(strXmlRowLeft);
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    sbTemp.Append(strXmlCellLeft);
                    sbTemp.Append(captions[k]);
                    sbTemp.Append(strXmlCellRight);
                }
                sbTemp.Append(strXmlRowRight);



                foreach (DataRow item in dt.Rows)
                {

                    sbTemp.Append(strXmlRowLeft);
                    for (int l = 0; l < dt.Columns.Count; l++)
                    {
                        string ColumnName = dt.Columns[l].ColumnName;
                        sbTemp.Append(strXmlCellLeft);

                        sbTemp.Append(item[l]);
                        sbTemp.Append(strXmlCellRight);
                    }

                    sbTemp.Append(strXmlRowRight);
                }
                sbTemp.Append(strXmlRight);
                return sbTemp.ToString();
            }
        }
























        /// 判断数据类型
        /// </summary>
        /// <param name="type">数据类型</param>
        /// <param name="sbTemp">时间临时变量</param>
        /// <param name="dtTemp">当前表</param>
        /// <param name="dr">当前行</param>
        /// <param name="j">当前列</param>
        public void IsType(string type, StringBuilder sbTemp, DateTime dtTemp, DataRow dr, int j)
        {
            if (type == "System.DateTime")
            {
                dtTemp = (DateTime)dr[j];
                sbTemp.Append(strXmlCellLeft);
                sbTemp.Append(dtTemp.ToShortDateString());
                sbTemp.Append(strXmlCellRight);
            }
            else if (type == "System.Int32" || type == "System.Decimal")
            {
                sbTemp.Append(_strXmlCellLeft_Number);
                sbTemp.Append(dr[j].ToString());
                sbTemp.Append(_strXmlCellRight_Number);
            }
            else
            {
                sbTemp.Append(strXmlCellLeft);
                sbTemp.Append(dr[j].ToString());
                sbTemp.Append(strXmlCellRight);
            }
        }

        /// <summary>
        /// 显示的字体大小
        /// </summary>
        public enum FontSize
        {
            /// <summary>
            /// owc原来的默认字体：10
            /// </summary>
            small,
            /// <summary>
            /// 中等字体：16
            /// </summary>
            normal,
            /// <summary>
            /// 大字体：20
            /// </summary>
            large
        }

        /// <summary>
        /// 单元格中的数据类型
        /// </summary>
        private enum DataType
        {
            OWC_str,
            OWC_num,
            OWC_none
        }

        #region 私有变量

        private OWC_Excel.FontSize _FontSize;

        private string _strXmlLeft;

        private string _strXmlRight;

        private string _strXmlRowLeft;

        private string _strXmlRowRight;

        private string _strXmlCellLeft;

        private string _strXmlCellRight;

        private string _strXmlCellLeft_Number;

        private string _strXmlCellRight_Number;

        #endregion

        #region 私有变量对应属性

        /// <summary>
        /// 单元格中的数据类型的字符表示
        /// </summary>
        /// <param name="dt">数据类型</param>
        /// <returns>单元格中的数据类型的字符表示</returns>
        private string GetDataTypeString(DataType dt)
        {
            string temp;
            switch (dt)
            {
                case DataType.OWC_str:
                    temp = "x:str";
                    break;
                case DataType.OWC_num:
                    temp = "x:num";
                    break;
                default:
                    temp = "";
                    break;
            }
            return temp;
        }

        /// <summary>
        /// 字体大小字符表示
        /// </summary>
        private string _xmlFontSize
        {
            get
            {
                switch (this._FontSize)
                {
                    case OWC_Excel.FontSize.small:
                        return "12pt";
                    case OWC_Excel.FontSize.normal:
                        return "16pt";
                    case OWC_Excel.FontSize.large:
                        return "20pt";
                    default:
                        return "12pt";
                }

            }
        }

        private string _xmlX_Height
        {
            get
            {
                switch (this._FontSize)
                {
                    case OWC_Excel.FontSize.small:
                        return "7620";
                    case OWC_Excel.FontSize.normal:
                        return "10583";
                    case OWC_Excel.FontSize.large:
                        return "10583";
                    default:
                        return "7620";
                }
            }
        }

        private string _xmlX_Width
        {
            get
            {
                switch (this._FontSize)
                {
                    case OWC_Excel.FontSize.small:
                        return "15240";
                    case OWC_Excel.FontSize.normal:
                        return "18521";
                    case OWC_Excel.FontSize.large:
                        return "18521";
                    default:
                        return "15240";
                }

            }
        }

        //默认行高
        private string _xmlX_DefaultRowHeight
        {
            get
            {
                switch (this._FontSize)
                {
                    case OWC_Excel.FontSize.small:
                        return "210";
                    case OWC_Excel.FontSize.normal:
                        return "360";
                    case OWC_Excel.FontSize.large:
                        return "465";
                    default:
                        return "210";
                }
            }
        }

        private string _xmlTr_height
        {
            get
            {
                switch (this._FontSize)
                {
                    case OWC_Excel.FontSize.small:
                        return "18";
                    case OWC_Excel.FontSize.normal:
                        return "24";
                    case OWC_Excel.FontSize.large:
                        return "31";
                    default:
                        return "18";
                }

            }
        }

        /// <summary>
        /// 最外层XML头
        /// </summary>
        private string strXmlLeft
        {
            get
            {
                return _strXmlLeft;
            }
        }

        /// <summary>
        /// 最外层XML尾
        /// </summary>
        private string strXmlRight
        {
            get
            {
                return _strXmlRight;
            }
        }

        /// <summary>
        /// 一行数据XML头
        /// </summary>
        private string strXmlRowLeft
        {
            get
            {
                return _strXmlRowLeft;
            }
        }

        /// <summary>
        /// 一行数据XML尾
        /// </summary>
        private string strXmlRowRight
        {
            get
            {
                return _strXmlRowRight;
            }
        }

        /// <summary>
        /// 单元格字符串XML头
        /// </summary>
        private string strXmlCellLeft
        {
            get
            {
                return _strXmlCellLeft;
            }
        }

        /// <summary>
        /// 单元格字符串XML尾
        /// </summary>
        private string strXmlCellRight
        {
            get
            {
                return _strXmlCellRight;
            }
        }

        /// <summary>
        /// 单元格数字XML头
        /// </summary>
        private string strXmlCellLeft_Number
        {
            get
            {
                return _strXmlCellLeft_Number;
            }
        }

        /// <summary>
        /// 单元格数字XML尾
        /// </summary>
        private string strXmlCellRight_Number
        {
            get
            {
                return _strXmlCellRight_Number;
            }
        }

        #endregion

        /// <summary>
        /// 得到表格的XML头
        /// <param name="intCols">列数</param>
        ///  <returns>XML字符串</returns>
        /// </summary>
        private string GetTableTitle(int intCols)
        {
            return this.GetTableTitle(intCols, DataType.OWC_none);
        }

        /// <summary>
        /// 得到表格的XML头
        /// </summary>
        /// <param name="intCols">列数</param>
        /// <param name="dataType">数据类型</param>
        /// <returns></returns>
        private string GetTableTitle(int intCols, DataType dataType)
        {
            StringBuilder sb = new StringBuilder();
            int i;

            sb.Append("<table class=wc4680812 " + this.GetDataTypeString(dataType) + ">");
            for (i = 0; i < intCols; i++)
            {
                sb.Append(" <col class=wc4680812 width=\"72\"/>");
            }
            return sb.ToString();
        }

        /// <summary>
        /// 得到空表格的XML				
        /// </summary>
        ///  <returns>XML字符串</returns>
        private string GetEmptyXML()
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(strXmlLeft);
            sb.Append(GetTableTitle(1));
            sb.Append(strXmlRowLeft);
            sb.Append(strXmlCellLeft);
            sb.Append(strXmlCellRight);
            sb.Append(strXmlRowRight);
            sb.Append(strXmlRight);

            return sb.ToString();
        }
    }
}
